﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EmoneyWeiXinEntity;
using System.Data.SqlClient;
using EmoneyWeiXinCommon;
using System.Data;

namespace EmoneyWeiXinDAL
{
    public class DailyTaskDAL
    {
        public static int AddOneDailyTask(DailyTaskEntity entity)
        {
            if (string.IsNullOrEmpty(entity.openid))
            {
                return 0;
            }
            string sql = "proc_AddDailyTask";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",entity.openid),
              new SqlParameter("@reprotId",entity.ReportId),
              new SqlParameter("@TaskType",entity.TaskType),
              new SqlParameter("@ydGold",entity.ydGold),
              new SqlParameter("@weixinId",entity.weixinId)
            };
            return SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, System.Data.CommandType.StoredProcedure, sql, paras);

        }
        /// <summary>
        /// 根据
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public static List<string> GetSignDateByOpenId(string openid)
        {
            string sql = "select distinct CreateDate from DailyTask where openid=@openid and TaskType=1 order by CreateDate desc";
            SqlParameter[] paras = 
            {
              new SqlParameter("openid",openid)
            };
            DataTable dt = SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, CommandType.Text, sql, paras);
            List<string> lsts = new List<string>();
            foreach (DataRow item in dt.Rows)
            {
                lsts.Add(item[0].ToString());
            }
            return lsts;
        }

        public static GoldUserEntity GetGoldUser(string openid)
        {
            string sql = "SELECT Id,openid,SumGold,Balance,CreateTime FROM GoldUser where openid=@openid";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid)
            };
            return SQLHelper.GetDataByColumns<GoldUserEntity>(Config.EmoneyWeiXin, sql, paras);
        }


        public static int AddDailyTaskZjJilu(string openid, string gameType, string winningType, int payGold, double getMoney, out int syGold)
        {
            string sql = "proc_AddDailyTaskZjJilu";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid),
              new SqlParameter("@WinningType",winningType),
              new SqlParameter("@payGold",payGold),
              new SqlParameter("@getMoney",getMoney),
              new SqlParameter("@GameType",gameType),
              new SqlParameter("@syGold",SqlDbType.Int)
            };
            paras[5].Direction = ParameterDirection.Output;
            int count = SQLHelper.ExecuteNonQuery(Config.EmoneyWeiXin, CommandType.StoredProcedure, sql, paras);
            syGold = Convert.ToInt32(paras[5].Value);
            return count;
        }

        public static int GetAllMoney(string openid, string orderNum, SqlTransaction tran, out double money)
        {
            string sql = "proc_DailyTaskTixian";
            SqlParameter[] paras = 
            {
               new SqlParameter("@openid",openid),
               new SqlParameter("@getMoney",SqlDbType.Money),
               new SqlParameter("@orderNum",orderNum)
            };
            paras[1].Direction = ParameterDirection.Output;
            int count = SQLHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, sql, paras);
            money = Convert.ToDouble(paras[1].Value);
            return count;
        }

        public static List<DailyTaskEntity> GetDailyTaskByPage(string openid, int page, out int total)
        {
            string sql = @" select * from 
                        (
                         Select *,ROW_NUMBER() over (order by CreateTime DESC) rowNum from
                           (
                           select openid,TaskType,GoldNum,CreateTime from DailyTask where openid=@openid  AND GoldNum>0
                           union all
                           select openid,4,PayGold ,CreateTime from WinningRecord where openid=@openid 
                           ) as tb2
                        ) as tb where rowNum between " + ((page - 1) * 20 + 1) + " and " + page * 20 + ";";

            sql += @"select COUNT(1) from(
                     select openid,TaskType,GoldNum,CreateTime from DailyTask where openid=@openid  AND GoldNum>0
                     union all
                     select openid,4,PayGold ,CreateTime from WinningRecord where openid=@openid 
                     ) as tb";

            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid)
            };
            DataSet ds = SQLHelper.ExecuteGetDataSet(Config.EmoneyWeiXin, CommandType.Text, sql, paras);
            DataTable table = ds.Tables[0];
            total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            List<DailyTaskEntity> list = SQLHelper.DataConvert<DailyTaskEntity>(table);
            return list;
        }

        public static List<GoldUserTradingRecordEntity> GetTradingRecordListByPage(string openid, int page, out int total)
        {
            string sql = @"  SELECT * FROM
  (
   SELECT *,ROW_NUMBER() over (order by CreateTime DESC) rowNum from GoldUserTradingRecord where ActiveMoney>0 And openid=@openid
  ) as tb where rowNum between " + ((page - 1) * 20 + 1) + " and " + page * 20 + @"
  SELECT COUNT(1) FROM GoldUserTradingRecord WHERE ActiveMoney>0 And openid=@openid";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid)
            };
            DataSet ds = SQLHelper.ExecuteGetDataSet(Config.EmoneyWeiXin, CommandType.Text, sql, paras);
            DataTable table = ds.Tables[0];
            total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            List<GoldUserTradingRecordEntity> list = SQLHelper.DataConvert<GoldUserTradingRecordEntity>(table);
            return list;
        }

        public static DataTable GetUserMainInfo(string openid)
        {
            string sql = @"SELECT tb1.fscs,tb1.ydcs,tb1.openid,tb2.nickname,tb2.headimgurl,ISNULL( tb3.Balance,0) Balance,ISNULL(tb3.SumGold,0) SumGold  FROM (SELECT @openid openid, (SELECT COUNT(1) FROM DailyTask where TaskType=2 AND openid=@openid ) fscs,(SELECT COUNT(1) FROM DailyTask where TaskType=3 AND openid=@openid) ydcs) tb1
                           LEFT JOIN
                           WXUser tb2 ON tb1.openid=tb2.openid
                           LEFT JOIN 
                           GoldUser tb3 ON tb1.openid=tb3.openid";
            SqlParameter[] paras = 
            {
              new SqlParameter("@openid",openid)
            };
            DataTable dt = SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, CommandType.Text, sql, paras);
            return dt;

        }

        public static DataTable GetTjData()
        {
            string sql = @"select 
                          (select COUNT(1) from GoldUser with(nolock)) cyrs,
                          (select SUM( getMoney) from WinningRecord with(nolock)) zjje,
                          (select COUNT(distinct openid) from WinningRecord  with(nolock)) cjrs,
                          (select COUNT(1) from GoldUser with(nolock) where SumGold>=500 ) dy500,
                          (select COUNT(1) from  DailyTask  with(nolock) where TaskType=1) qdcs,
                          (select COUNT(1) from  DailyTask with(nolock) where TaskType=2 ) fscs,
                          (select COUNT(1) from  DailyTask with(nolock) where TaskType=3 ) ydcs";
            DataTable dt = SQLHelper.ExecuteGetTable(Config.EmoneyWeiXin, CommandType.Text, sql, null);
            return dt;
        }

    }
}
